﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using ColumnsInformation;
using System.Data;
using System.Data.SqlClient;
using Cfg;

namespace MyDataProvider
{
    static public class MSSQL
    {
        public class Authorization
        {
            public enum AuthorizationType { None, WindowsAuth, SQLAuth };
            AuthorizationType authType = AuthorizationType.None;
            public string UserId
            {
                get;
                set;
            }
            public string Password
            {
                get;
                set;
            }
            public AuthorizationType Type
            {
                get
                {
                    return authType;
                }
                set
                {
                    authType = value;
                }
            }
        }   

        public static SqlConnectionStringBuilder GetConnectionString(SqlConnectionConfig cfg)
        {
            SqlConnectionStringBuilder connString = new SqlConnectionStringBuilder();
            connString.DataSource = cfg.DataSource;
            if (cfg.Auth.Type == Authorization.AuthorizationType.SQLAuth)
            {
                connString.IntegratedSecurity = false;
                connString.UserID = cfg.Auth.UserId;
                connString.Password = cfg.Auth.Password;
            }
            else
                if (cfg.Auth.Type == Authorization.AuthorizationType.WindowsAuth)
                    connString.IntegratedSecurity = true;
            if (cfg.Database != "")
                connString.InitialCatalog = cfg.Database;
            return connString;
        }
        public static bool CheckConnection(SqlConnectionStringBuilder sqlConnString)
        {
            sqlConnString.InitialCatalog = "Master";
            string connString = sqlConnString.ToString();
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    string query = "select COUNT(*) from INFORMATION_SCHEMA.TABLES";
                    SqlCommand command = new SqlCommand(query, conn);
                    int result = (int)command.ExecuteScalar();
                    conn.Close();
                    return true;
                }
            }
            catch (Exception ex) // catch (SqlException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
                //return false;
            }
        }
        public static List<string> GetDatabases(SqlConnectionStringBuilder sqlConnString)
        {
            sqlConnString.InitialCatalog = "Master";
            string connString = sqlConnString.ToString();
            List<string> databases = null;
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    databases = new List<string>();
                    System.Data.DataTable dbSchema = conn.GetSchema("Databases");
                    foreach (System.Data.DataRow row in dbSchema.Rows)
                    {
                        databases.Add(row["database_name"].ToString());
                    }
                    conn.Close();
                }
            }
            catch (Exception ex) // catch (SqlException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
            if (databases != null)
                databases.Sort();

            return databases;
        }
        public static ColumnsInfo GetColumnsSchemaByQueryToSchema(SqlConnectionStringBuilder sqlConnString, string database, string schema, string table)
        {
            string connString = sqlConnString.ToString();
            string query = "SELECT COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '@TABLE_SCHEMA' and TABLE_NAME = '@TABLE_NAME'";
            query = query.Replace("@TABLE_SCHEMA", schema);
            query = query.Replace("@TABLE_NAME", table);
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand(query, conn);
                    SqlDataReader reader = command.ExecuteReader();
                    ColumnsInfo columnsInfo = new ColumnsInfo();
                    while (reader.Read())
                    {
                        ColumnInfo info = new ColumnInfo()
                        {
                            Name = reader["COLUMN_NAME"].ToString(),
                            Type = reader.GetSqlValue(1).ToString(), //["DATA_TYPE"].ToString(),
                            Default = reader["COLUMN_DEFAULT"].ToString(),
                            IsNullable = reader["IS_NULLABLE"].ToString() == "YES" ? true : false
                        };
                        columnsInfo.Add(info);

                    }
                    conn.Close();
                    columnsInfo.ColumnsWithPrimaryKey = GetColumnWithPrimaryKey(sqlConnString, schema, table);
                    return columnsInfo;
                }
            }
            catch (SqlException ex) // catch (SqlException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
            catch (InvalidOperationException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
        }
        private static string GetColumnWithPrimaryKey(SqlConnectionStringBuilder sqlConnString, string schema, string table)
        {
            string connString = sqlConnString.ToString();
            string query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '@TABLE_SCHEMA' and TABLE_NAME = '@TABLE_NAME' and CONSTRAINT_NAME =" +
                           "(SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = '@TABLE_SCHEMA' and TABLE_NAME = '@TABLE_NAME' AND CONSTRAINT_TYPE = 'PRIMARY KEY')";
            query = query.Replace("@TABLE_SCHEMA", schema);
            query = query.Replace("@TABLE_NAME", table);

            string columnWithPrimaryKey = "";
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand(query, conn);
                    columnWithPrimaryKey = (string)command.ExecuteScalar();
                    conn.Close();
                    return columnWithPrimaryKey;

                }
            }
            catch (SqlException ex) // catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                throw ex;
            }
            catch (InvalidOperationException ex)
            {
                Console.WriteLine(ex.Message);
                throw ex;
            }
        }
        public static System.Data.DataTable GetTables(SqlConnectionStringBuilder sqlConnString)
        {
            string connString = sqlConnString.ToString();
            System.Data.DataTable tableSchema = null;
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    tableSchema = conn.GetSchema("Tables"); //, new string[]{null, null, null, "BASE TABLE"});
                    conn.Close();
                }
            }
            catch (Exception ex) // catch (SqlException ex)
            {
                Debug.WriteLine(ex.Message);
            }
            return tableSchema;
        }
        public static System.Data.DataTable Transpose(System.Data.DataTable originTable)
        {
            string str = "";
            System.Data.DataTable tableTransposed = null;
            try
            {
                tableTransposed = new System.Data.DataTable(originTable.TableName);

                foreach (DataRow row in originTable.Rows)
                {
                    tableTransposed.Columns.Add(row["COLUMN_NAME"].ToString(), str.GetType());
                }
                object[] ob = new object[originTable.Rows.Count];
                int i = 0;
                foreach (DataRow row in originTable.Rows)
                {
                    ob[i] = row["DATA_TYPE"].ToString();
                    i++;
                }
                tableTransposed.Rows.Add(ob);
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
            }
            return tableTransposed;
        }

        public static object[,] GetTableContent(SqlConnectionStringBuilder sqlConnString, string database, string schema, string table, ColumnsInfo columnsInfo, string[] columnsForShow, int row_start, int row_end)
        {
            string connString = sqlConnString.ToString();
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    int cntEntries = MSSQL.GetNumOfEntries(sqlConnString, database, schema, table);
                    string colWithPrimaryKey = columnsInfo.ColumnsWithPrimaryKey;
                    if (colWithPrimaryKey == "")
                    {
                        colWithPrimaryKey = columnsInfo[0].Name;
                    }
                    string query = "SELECT @COLUMNS FROM (SELECT @COLUMNS, ROW_NUMBER() over (ORDER BY [@PRIMARY_COLUMN]) as ROW FROM [@DATABASE].[@SCHEMA].[@TABLE]) a WHERE  ROW >= @ROW_START and ROW <= @ROW_END";
                    string selectWhat = "";
                    if (columnsForShow != null)
                    {
                        foreach (string what in columnsForShow)
                            selectWhat += "[" + what + "]" + ",";
                        selectWhat = selectWhat.Substring(0, selectWhat.Length - 1);
                    }
                    else
                    {
                        foreach (ColumnInfo what in columnsInfo)
                            selectWhat += "[" + what.Name + "]" + ",";
                        selectWhat = selectWhat.Substring(0, selectWhat.Length - 1);
                    }
                    query = query.Replace("@COLUMNS", selectWhat);
                    query = query.Replace("@PRIMARY_COLUMN", colWithPrimaryKey);
                    query = query.Replace("@DATABASE", database);
                    query = query.Replace("@SCHEMA", schema);
                    query = query.Replace("@TABLE", table);
                    query = query.Replace("@ROW_START", row_start.ToString());
                    query = query.Replace("@ROW_END", row_end.ToString());
                    conn.Open();
                    SqlCommand command = new SqlCommand(query, conn);
                    SqlDataReader dataReader = command.ExecuteReader();
                    int size = row_end - row_start + 1;
                    if (size > cntEntries)
                        size = cntEntries;
                    object[,] result = GetObjectFromSqlDataReader(dataReader, size, columnsInfo);
                    conn.Close();
                    return result;
                }
            }
            catch (SqlException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
            catch (InvalidOperationException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
        }
        public static DataSet GetTableContentAsDataset(SqlConnectionStringBuilder sqlConnString, string database, string schema, string table, ColumnsInfo columnsInfo, string[] columnsForShow, int row_start, int row_end)
        {
            string connString = sqlConnString.ToString();
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    int cntEntries = MSSQL.GetNumOfEntries(sqlConnString, database, schema, table);
                    string colWithPrimaryKey = columnsInfo.ColumnsWithPrimaryKey;
                    if (colWithPrimaryKey == null)
                    {
                        colWithPrimaryKey = columnsInfo[0].Name;
                    }
                    string query = "SELECT @COLUMNS FROM (SELECT @COLUMNS, ROW_NUMBER() over (ORDER BY [@PRIMARY_COLUMN]) as ROW FROM [@DATABASE].[@SCHEMA].[@TABLE]) a WHERE  ROW >= @ROW_START and ROW <= @ROW_END";
                    string selectWhat = "";
                    if (columnsForShow != null)
                    {
                        foreach (string what in columnsForShow)
                            selectWhat += "[" + what + "]" + ",";
                        selectWhat = selectWhat.Substring(0, selectWhat.Length - 1);
                    }
                    else
                    {
                        foreach (ColumnInfo what in columnsInfo)
                            selectWhat += "[" + what.Name + "]" + ",";
                        selectWhat = selectWhat.Substring(0, selectWhat.Length - 1);
                    }
                    query = query.Replace("@COLUMNS", selectWhat);
                    query = query.Replace("@PRIMARY_COLUMN", colWithPrimaryKey);
                    query = query.Replace("@DATABASE", database);
                    query = query.Replace("@SCHEMA", schema);
                    query = query.Replace("@TABLE", table);
                    query = query.Replace("@ROW_START", row_start.ToString());
                    query = query.Replace("@ROW_END", row_end.ToString());
                    conn.Open();
                    SqlCommand command = new SqlCommand(query, conn);
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                    DataSet dataSet = new DataSet();
                    dataAdapter.Fill(dataSet);
                    conn.Close();
                    return dataSet;
                }
            }
            catch (SqlException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
            catch (InvalidOperationException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
        }
        public static object[,] GetObjectFromSqlDataReader(SqlDataReader dataReader, int rowSize, ColumnsInfo columnInfo)
        {
            try
            {
                int nCol = dataReader.FieldCount;
                object[,] result = new object[rowSize, nCol];
                int i = 0, j = 0;
                
                while (dataReader.Read())
                {
                    for (j = 0; j < nCol; j++)
                    {
                        result[i, j] = dataReader.GetSqlValue(j);
                    }
                    i++;
                }
                return result;
            }
            catch (SqlException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
            catch (InsufficientMemoryException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
            catch (InvalidOperationException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
            catch (ArgumentException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
        }
        public static int GetNumOfEntries(SqlConnectionStringBuilder sqlConnString, string database, string schema, string table)
        {
            string connString = sqlConnString.ToString();
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    string query = "SELECT COUNT(*) FROM [@DATABASE].[@SCHEMA].[@TABLE]";
                    query = query.Replace("@DATABASE", database);
                    query = query.Replace("@SCHEMA", schema);
                    query = query.Replace("@TABLE", table);
                    conn.Open();
                    SqlCommand command = new SqlCommand(query, conn);
                    return (int)command.ExecuteScalar();
                }
            }
            catch (SqlException ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }
        }
    }
}
